﻿/******************************************************   
创建者: ljj
创建时间: 2018-10-12
修改:
修改时间: 
功能: 指标数据-日周月年累计
 ******************************************************/

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using com.nm;
using com.nm.ApiServer;
using NHibernateVertica;
using com.nm.ApiServer.Attributes;
using com.nm.Attributes;
using com.nm.db.Models.Hkerp;
using com.nm.library.Helper;
using WeiXinAPI.model;

namespace WeiXinAPI
{
    [Description("指标数据-日周月年累计")]
    public class WxDataIndicatorsHandler : BaseHandler
    {
        public VerticaDataHelper Helper { get; set; } = new VerticaDataHelper();

        private static readonly string connection = ConfigurationManager.ConnectionStrings["Vertica21(2)"].ConnectionString;

        //[MustLogin]
        [Description("获取指标数据报表")]
        [ReturnType(typeof(WxDataIndicatorsModel.ListModel))]
        public ApiResult GetReport([ParamDescription("报表类型")] int ReportType = 0)
        {
            try
            {
                var time = DateTime.Today.AddDays(-1);
                var result = new WxDataIndicatorsModel.ListModel();
                switch (ReportType)
                {
                    case 0:
                        result.saleData = GetDaySaleData(time);
                        result.areaData = GetDayAreaData(time);
                        result.trendData = GetDaySeqTrend(time.AddDays(-5), time);
                        break;
                    case 1:
                        result.saleData = GetWeekSaleData(time);
                        result.areaData = GetWeekAreaData(time);
                        var weekList = new List<string>
                        {
                            $"'{time:yyyy-MM-dd}'",
                        };
                        var lastSunday = time.AddDays(1 - (int) time.DayOfWeek).AddDays(-1);
                        for (var i = 0; i < 5; i++)
                        {
                            weekList.Add($"'{lastSunday.AddDays(-i * 7):yyyy-MM-dd}'");
                        }
                        result.trendData = GetWeekSeqTrend(weekList);
                        break;
                    case 2:
                        result.saleData = GetMonthSaleData(time);
                        result.areaData = GetMonthAreaData(time);
                        var monthList = new List<string>
                        {
                            $"'{time:yyyy-MM-dd}'",
                        };
                        var lastMonth = new DateTime(time.Year, time.Month, 1).AddDays(-1);
                        for (var i = 0; i < 5; i++)
                        {
                            monthList.Add($"'{lastMonth.AddMonths(-i):yyyy-MM-dd}'");
                        }
                        result.trendData = GetMonthSeqTrend(monthList);
                        break;
                    case 3:
                        result.saleData = GetYearSaleData(time);
                        result.areaData = GetYearAreaData(time);
                        result.trendData = new List<WxDataIndicatorsModel.SeqTrendModel>();
                        break;
                    default: break;
                }
                if (result.saleData != null)
                {
                    result.saleData.AchievAmt = Math.Round(result.saleData.AchievAmt / 10000, 1, MidpointRounding.AwayFromZero);
                    result.saleData.SaleQty = Convert.ToInt64(Math.Round(result.saleData.SaleQty / 10000.0, 1, MidpointRounding.AwayFromZero));
                    result.saleData.CT = Convert.ToInt64(Math.Round(result.saleData.CT / 10000.0, 1, MidpointRounding.AwayFromZero));
                    result.saleData.CN = Convert.ToInt64(Math.Round(result.saleData.CN / 10000.0, 1, MidpointRounding.AwayFromZero));
                    result.saleData.LAchievAmt = Math.Round(result.saleData.LAchievAmt / 10000, 1, MidpointRounding.AwayFromZero);
                    result.saleData.LSaleQty = Convert.ToInt64(Math.Round(result.saleData.LSaleQty / 10000.0, 1, MidpointRounding.AwayFromZero));
                    result.saleData.LCT = Convert.ToInt64(Math.Round(result.saleData.LCT / 10000.0, 1, MidpointRounding.AwayFromZero));
                    result.saleData.LCN = Convert.ToInt64(Math.Round(result.saleData.LCN / 10000.0, 1, MidpointRounding.AwayFromZero));
                    result.saleData.ConverRate = Math.Round(result.saleData.ConverRate * 100, 1, MidpointRounding.AwayFromZero);
                    result.saleData.JointRate = Math.Round(result.saleData.JointRate, 2, MidpointRounding.AwayFromZero);
                    result.saleData.UnitPrice = Math.Round(result.saleData.UnitPrice, 1, MidpointRounding.AwayFromZero);
                    result.saleData.CustomerPrice = Math.Round(result.saleData.CustomerPrice, 1, MidpointRounding.AwayFromZero);
                    result.saleData.LConverRate = Math.Round(result.saleData.LConverRate * 100, 1, MidpointRounding.AwayFromZero);
                    result.saleData.LJointRate = Math.Round(result.saleData.LJointRate, 2, MidpointRounding.AwayFromZero);
                    result.saleData.LUnitPrice = Math.Round(result.saleData.LUnitPrice, 1, MidpointRounding.AwayFromZero);
                    result.saleData.LCustomerPrice = Math.Round(result.saleData.LCustomerPrice, 1, MidpointRounding.AwayFromZero);
                    result.saleData.GroAchievAmt = result.saleData.LAchievAmt == 0 ? 0 : Math.Round(result.saleData.AchievAmt * 100.0m / result.saleData.LAchievAmt, 2, MidpointRounding.AwayFromZero);
                    result.saleData.GroSaleQty = result.saleData.LSaleQty == 0 ? 0 : Math.Round(result.saleData.SaleQty * 100.0m / result.saleData.LSaleQty, 2, MidpointRounding.AwayFromZero);
                    result.saleData.GroCT = result.saleData.LCT == 0 ? 0 : Math.Round(result.saleData.CT * 100.0m / result.saleData.LCT, 2, MidpointRounding.AwayFromZero);
                    result.saleData.GroCN = result.saleData.LCN == 0 ? 0 : Math.Round(result.saleData.CN * 100.0m / result.saleData.LCN, 2, MidpointRounding.AwayFromZero);
                    result.saleData.GroConverRate = result.saleData.LConverRate == 0 ? 0 : Math.Round(result.saleData.ConverRate * 100.0m / result.saleData.LConverRate, 2, MidpointRounding.AwayFromZero);
                    result.saleData.GroJointRate = result.saleData.LJointRate == 0 ? 0 : Math.Round(result.saleData.JointRate * 100.0m / result.saleData.LJointRate, 2, MidpointRounding.AwayFromZero);
                    result.saleData.GroUnitPrice = result.saleData.LUnitPrice == 0 ? 0 : Math.Round(result.saleData.UnitPrice * 100.0m / result.saleData.LUnitPrice, 2, MidpointRounding.AwayFromZero);
                }
                if (result.areaData != null)
                {
                    foreach (var item in result.areaData)
                    {
                        item.AchievAmt = Math.Round(item.AchievAmt / 10000, 1, MidpointRounding.AwayFromZero);
                        item.SaleQty = Convert.ToInt64(Math.Round(item.SaleQty / 10000.0, 1, MidpointRounding.AwayFromZero));
                        item.CT = Convert.ToInt64(Math.Round(item.CT / 10000.0, 1, MidpointRounding.AwayFromZero));
                        item.CN = Convert.ToInt64(Math.Round(item.CN / 10000.0, 1, MidpointRounding.AwayFromZero));
                        item.LAchievAmt = Math.Round(item.LAchievAmt / 10000, 1, MidpointRounding.AwayFromZero);
                        item.LSaleQty = Convert.ToInt64(Math.Round(item.LSaleQty / 10000.0, 1, MidpointRounding.AwayFromZero));
                        item.LCT = Convert.ToInt64(Math.Round(item.LCT / 10000.0, 1, MidpointRounding.AwayFromZero));
                        item.LCN = Convert.ToInt64(Math.Round(item.LCN / 10000.0, 1, MidpointRounding.AwayFromZero));
                        item.ConverRate = Math.Round(item.ConverRate * 100, 1, MidpointRounding.AwayFromZero);
                        item.JointRate = Math.Round(item.JointRate, 2, MidpointRounding.AwayFromZero);
                        item.UnitPrice = Math.Round(item.UnitPrice, 1, MidpointRounding.AwayFromZero);
                        item.CustomerPrice = Math.Round(item.CustomerPrice, 1, MidpointRounding.AwayFromZero);
                        item.LConverRate = Math.Round(item.LConverRate * 100, 1, MidpointRounding.AwayFromZero);
                        item.LJointRate = Math.Round(item.LJointRate, 2, MidpointRounding.AwayFromZero);
                        item.LUnitPrice = Math.Round(item.LUnitPrice, 1, MidpointRounding.AwayFromZero);
                        item.LCustomerPrice = Math.Round(item.LCustomerPrice, 1, MidpointRounding.AwayFromZero);
                        item.SeqAchievAmt = item.LAchievAmt == 0 ? 0 : Math.Round((item.AchievAmt - item.LAchievAmt) * 100.0m / item.LAchievAmt, 2, MidpointRounding.AwayFromZero);
                        item.SeqSaleQty = item.LSaleQty == 0 ? 0 : Math.Round((item.SaleQty - item.LSaleQty) * 100.0m / item.LSaleQty, 2, MidpointRounding.AwayFromZero);
                        item.SeqCT = item.LCT == 0 ? 0 : Math.Round((item.CT - item.LCT) * 100.0m / item.LCT, 2, MidpointRounding.AwayFromZero);
                        item.SeqCN = item.LCN == 0 ? 0 : Math.Round((item.CN - item.LCN) * 100.0m / item.LCN, 2, MidpointRounding.AwayFromZero);
                        item.SeqConverRate = item.LConverRate == 0 ? 0 : Math.Round((item.ConverRate - item.LConverRate) * 100.0m / item.LConverRate, 2, MidpointRounding.AwayFromZero);
                        item.SeqJointRate = item.LJointRate == 0 ? 0 : Math.Round((item.JointRate - item.LJointRate) * 100.0m / item.LJointRate, 2, MidpointRounding.AwayFromZero);
                        item.SeqUnitPrice = item.LUnitPrice == 0 ? 0 : Math.Round((item.UnitPrice - item.LUnitPrice) * 100.0m / item.LUnitPrice, 2, MidpointRounding.AwayFromZero);
                    }
                }
                foreach (var item in result.trendData)
                {
                    item.AchievAmt = Math.Round(item.AchievAmt / 10000, 1, MidpointRounding.AwayFromZero);
                    item.SaleQty = Convert.ToInt64(Math.Round(item.SaleQty / 10000.0, 1, MidpointRounding.AwayFromZero));
                    item.CT = Convert.ToInt64(Math.Round(item.CT / 10000.0, 1, MidpointRounding.AwayFromZero));
                    item.CN = Convert.ToInt64(Math.Round(item.CN / 10000.0, 1, MidpointRounding.AwayFromZero));
                    item.ConverRate = Math.Round(item.ConverRate * 100, 1, MidpointRounding.AwayFromZero);
                    item.JointRate = Math.Round(item.JointRate, 2, MidpointRounding.AwayFromZero);
                    item.UnitPrice = Math.Round(item.UnitPrice, 1, MidpointRounding.AwayFromZero);
                }
                return Success(result);
            }
            catch (Exception ex)
            {
                Log.Error(ex);
                return Error("获取数据失败！");
            }
        }

        /// <summary>
        /// 日报，取本日的数据
        /// </summary>
        /// <returns></returns>
        public WxDataIndicatorsModel.LSaleDataModel GetDaySaleData(DateTime Time)
        {
            try
            {
                var sql = $@"------=================日报，取本日的数据========================------
with cte as(
  select OperationDate,
         sum(DAchievAmt) as DAchievAmt,   --销售金额
         sum(LDAchievAmt) as LDAchievAmt,   --上期销售金额
         sum(DSaleQty) as DSaleQty,        --销售数量       
         sum(LDSaleQty) as LDSaleQty        --上期销售数量   
    from aps.aps_kind_sale_ratio
   where OperationDate = '{Time:yyyy-MM-dd}'     --传参，默认取昨天的日期
   group by OperationDate
          )
    ,cte1 as(
  select OperationDate,
         sum(DCT) as DCT,                 --客流量
         sum(LDCT) as LDCT,               --上期客流量
         sum(DCN) as DCN,                 --客单数
         sum(LDCN) as LDCN                --上期客单数
    from aps.aps_shop_cnct_ratio
   where OperationDate = '{Time:yyyy-MM-dd}'     --传参，默认取昨天的日期
   group by OperationDate
            )
  select to_char(cte.OperationDate,'YYYY-MM-DD') as OperationDate,               --销售日期
         cte.DAchievAmt as AchievAmt,                  --销售金额
         cte.LDAchievAmt as LAchievAmt,                   --上期销售金额
         cte.DSaleQty as SaleQty,                      --销售数量
         cte.LDSaleQty as LSaleQty,                     --上期销售数量 
         cte1.DCT as CT,                          --客流量
         cte1.LDCT as LCT,                         --上期客流量
         cte1.DCN as CN,                          --客单数
         cte1.LDCN as LCN,                         --上期客单数
         cte1.DCN / case when cte1.DCT = 0 then 1 else cte1.DCT end  as ConverRate,                   --客单数 /客流量=转换率
         cte1.LDCN / case when cte1.LDCT = 0 then 1 else cte1.LDCT end  as LConverRate,                --客单数 /客流量=上期转换率
         cte.DSaleQty / case when cte1.DCN = 0 then 1 else cte1.DCN end  as JointRate,                --销售数量/客单数=连带率 
         cte.LDSaleQty / case when cte1.LDCN = 0 then 1 else cte1.LDCN end  as LJointRate,             --销售数量/客单数=上期连带率
         cte.DAchievAmt / case when cte.DSaleQty = 0 then 1 else cte.DSaleQty end  as UnitPrice,      --销售金额/销售数量=件单价
         cte.LDAchievAmt / case when cte.LDSaleQty = 0 then 1 else cte.LDSaleQty end  as LUnitPrice,    --销售金额/销售数量=上期件单价
         cte.DAchievAmt / case when cte1.DCN = 0 then 1 else cte1.DCN end as CustomerPrice,  --销售金额/客单数=客单价
         cte.LDAchievAmt / case when cte1.LDCN = 0 then 1 else cte1.LDCN end as LCustomerPrice  --环比上日客单价
    from cte
    left join cte1 on cte.OperationDate = cte1.OperationDate";
                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd, connection);
                var list = ModelConvertHelper<WxDataIndicatorsModel.LSaleDataModel>.ConvertToModel(dt);
                return list.FirstOrDefault();
            }
            catch (Exception ex)
            {
                throw(ex);
            }
        }

        /// <summary>
        /// 周报，取截止到本日的周累计数据
        /// </summary>
        /// <returns></returns>
        public WxDataIndicatorsModel.LSaleDataModel GetWeekSaleData(DateTime Time)
        {
            try
            {
                var sql = $@"------===============周报，取截止到本日的周累计数据================------
with cte as(
 select OperationDate,
        sum(NWAchievAmt) as NWAchievAmt,   --销售金额
         sum(LNWAchievAmt) as LNWAchievAmt,   --上期销售金额
         sum(NWSaleQty) as NWSaleQty,         --销售数量       
         sum(LNWSaleQty) as LNWSaleQty        --上期销售数量  
   from aps.aps_kind_sale_ratio
  where OperationDate = '{Time:yyyy-MM-dd}'       --传参，默认取昨天的日期
  group by OperationDate
         )
   ,cte1 as(
 select OperationDate,
        sum(NWCT) as NWCT,                 --客流量
         sum(LNWCT) as LNWCT,               --上期客流量
         sum(NWCN) as NWCN,                 --客单数
         sum(LNWCN) as LNWCN                --上期客单数
   from aps.aps_shop_cnct_ratio
  where OperationDate = '{Time:yyyy-MM-dd}'       --传参，默认取昨天的日期
  group by OperationDate
           )
 select to_char(cte.OperationDate,'YYYY-MM-DD') as OperationDate,                 --销售日期
        cte.NWAchievAmt as AchievAmt,                   --销售金额
         cte.LNWAchievAmt as LAchievAmt,                   --上期销售金额
         cte.NWSaleQty as SaleQty,                      --销售数量
         cte.LNWSaleQty as LSaleQty,                     --上期销售数量 
         cte1.NWCT as CT,                          --客流量
         cte1.LNWCT as LCT,                         --上期客流量
         cte1.NWCN as CN,                          --客单数
         cte1.LNWCN as LCN,                         --上期客单数
         cte1.NWCN / case when cte1.NWCT = 0 then 1 else cte1.NWCT end  as ConverRate,                   --客单数 /客流量=转换率
         cte1.LNWCN / case when cte1.LNWCT = 0 then 1 else cte1.LNWCT end  as LConverRate,                 --客单数 /客流量=上期转换率
         cte.NWSaleQty / case when cte1.NWCN = 0 then 1 else cte1.NWCN end  as JointRate,                --销售数量/客单数=连带率 
         cte.LNWSaleQty / case when cte1.LNWCN = 0 then 1 else cte1.LNWCN end  as LJointRate,             --销售数量/客单数=上期连带率
         cte.NWAchievAmt / case when cte.NWSaleQty = 0 then 1 else cte.NWSaleQty end  as UnitPrice,      --销售金额/销售数量=件单价
         cte.LNWAchievAmt / case when cte.LNWSaleQty = 0 then 1 else cte.LNWSaleQty end  as LUnitPrice,    --销售金额/销售数量=上期件单价
         cte.NWAchievAmt / case when cte1.NWCN = 0 then 1 else cte1.NWCN end as CustomerPrice,  --销售金额/客单数=客单价
         cte.LNWAchievAmt / case when cte1.LNWCN = 0 then 1 else cte1.LNWCN end as LCustomerPrice  --环比上日客单价
   from cte
   left join cte1 on cte.OperationDate = cte1.OperationDate";
                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd, connection);
                var list = ModelConvertHelper<WxDataIndicatorsModel.LSaleDataModel>.ConvertToModel(dt);
                return list.FirstOrDefault();
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 月报，取截止到本日的月累计数据
        /// </summary>
        /// <returns></returns>
        public WxDataIndicatorsModel.LSaleDataModel GetMonthSaleData(DateTime Time)
        {
            try
            {
                var sql = $@"------===============月报，取截止到本日的月累计数据================------
with cte as(
  select OperationDate,
         sum(MAchievAmt) as MAchievAmt,   --销售金额
         sum(LMAchievAmt) as LMAchievAmt,   --上期销售金额
         sum(MSaleQty) as MSaleQty,         --销售数量       
         sum(LMSaleQty) as LMSaleQty        --上期销售数量  
    from aps.aps_kind_sale_ratio
   where OperationDate = '{Time:yyyy-MM-dd}'     --传参，默认取昨天的日期
   group by OperationDate
          )
    ,cte1 as(
  select OperationDate,
         sum(MCT) as MCT,                 --客流量
         sum(LMCT) as LMCT,               --上期客流量
         sum(MCN) as MCN,                 --客单数
         sum(LMCN) as LMCN                --上期客单数
    from aps.aps_shop_cnct_ratio
   where OperationDate = '{Time:yyyy-MM-dd}'     --传参，默认取昨天的日期
   group by OperationDate
            )
  select to_char(cte.OperationDate,'YYYY-MM-DD') as OperationDate,               --销售日期
         cte.MAchievAmt as AchievAmt,                  --销售金额
         cte.LMAchievAmt as LAchievAmt,                 --上期销售金额
         cte.MSaleQty as SaleQty,                    --销售数量
         cte.LMSaleQty as LSaleQty,                   --上期销售数量 
         cte1.MCT as CT,                        --客流量
         cte1.LMCT as LCT,                       --上期客流量
         cte1.MCN as CN,                        --客单数
         cte1.LMCN as LCN,                        --上期客单数
         cte1.MCN / case when cte1.MCT = 0 then 1 else cte1.MCT end  as ConverRate,                   --客单数 /客流量=转换率
         cte1.LMCN / case when cte1.LMCT = 0 then 1 else cte1.LMCT end  as LConverRate,                --客单数 /客流量=上期转换率
         cte.MSaleQty / case when cte1.MCN = 0 then 1 else cte1.MCN end  as JointRate,                --销售数量/客单数=连带率 
         cte.LMSaleQty / case when cte1.LMCN = 0 then 1 else cte1.LMCN end  as LJointRate,             --销售数量/客单数=上期连带率
         cte.MAchievAmt / case when cte.MSaleQty = 0 then 1 else cte.MSaleQty end  as UnitPrice,      --销售金额/销售数量=件单价
         cte.LMAchievAmt / case when cte.LMSaleQty = 0 then 1 else cte.LMSaleQty end  as LUnitPrice,    --销售金额/销售数量=上期件单价
         cte.MAchievAmt / case when cte1.MCN = 0 then 1 else cte1.MCN end as CustomerPrice,  --销售金额/客单数=客单价
         cte.LMAchievAmt / case when cte1.LMCN = 0 then 1 else cte1.LMCN end as LCustomerPrice  --环比上日客单价
    from cte
    left join cte1 on cte.OperationDate = cte1.OperationDate";
                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd, connection);
                var list = ModelConvertHelper<WxDataIndicatorsModel.LSaleDataModel>.ConvertToModel(dt);
                return list.FirstOrDefault();
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 年报，取截止到本日的年累计数据
        /// </summary>
        /// <returns></returns>
        public WxDataIndicatorsModel.LSaleDataModel GetYearSaleData(DateTime Time)
        {
            try
            {
                var sql = $@"------===============年报，取截止到本日的年累计数据================------
with cte as(
  select to_char(OperationDate,'YYYY') as OperationDate,
         sum(DAchievAmt) as DAchievAmt,   --销售金额
         0.0 as LDAchievAmt,                    --上期销售金额
         sum(DSaleQty) as DSaleQty,         --销售数量       
         0 as LDSaleQty                       --上期销售数量  
    from aps.aps_kind_sale_ratio
   where OperationDate >= '{Time.Year}-01-01'
     and OperationDate <= '{Time:yyyy-MM-dd}'     --传参，默认年度1月1日取昨天的日期
   group by to_char(OperationDate,'YYYY')
          )
    ,cte1 as(
  select to_char(OperationDate,'YYYY') OperationDate,
         sum(DCT) as DCT,                 --客流量
         0 as LDCT,                         --上期客流量
         sum(DCN) as DCN,                 --客单数
         0 as LDCN                          --上期客单数
    from aps.aps_shop_cnct_ratio
   where OperationDate >= '{Time.Year}-01-01'
     and OperationDate <= '{Time:yyyy-MM-dd}'     --传参，默认年度1月1日取昨天的日期
   group by to_char(OperationDate,'YYYY')
            )
  select cte.OperationDate,               --销售日期
         cte.DAchievAmt as AchievAmt,                  --销售金额
         cte.LDAchievAmt as LAchievAmt,                 --上期销售金额
         cte.DSaleQty as SaleQty,                    --销售数量
         cte.LDSaleQty as LSaleQty,                   --上期销售数量 
         cte1.DCT as CT,                        --客流量
         cte1.LDCT as LCT,                       --上期客流量
         cte1.DCN as CN,                        --客单数
         cte1.LDCN as LCN,                        --上期客单数
         cte1.DCN / case when cte1.DCT = 0 then 1 else cte1.DCT end  as ConverRate,                   --客单数 /客流量=转换率
         0.0  as LConverRate,                                                                            --客单数 /客流量=上期转换率
         cte.DSaleQty / case when cte1.DCN = 0 then 1 else cte1.DCN end  as JointRate,                --销售数量/客单数=连带率 
         0.0  as LJointRate,                                                                             --销售数量/客单数=上期连带率
         cte.DAchievAmt / case when cte.DSaleQty = 0 then 1 else cte.DSaleQty end  as UnitPrice,      --销售金额/销售数量=件单价
         0.0  as LUnitPrice,                                                                              --销售金额/销售数量=上期件单价
         cte.DAchievAmt / case when cte1.DCN = 0 then 1 else cte1.DCN end as CustomerPrice,  --销售金额/客单数=客单价
         cte.LDAchievAmt / case when cte1.LDCN = 0 then 1 else cte1.LDCN end as LCustomerPrice  --环比上日客单价
    from cte
    left join cte1 on cte.OperationDate = cte1.OperationDate";
                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd, connection);
                var list = ModelConvertHelper<WxDataIndicatorsModel.LSaleDataModel>.ConvertToModel(dt);
                return list.FirstOrDefault();
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 日报，取本日的数据
        /// </summary>
        /// <returns></returns>
        public IList<WxDataIndicatorsModel.AreaDataModel> GetDayAreaData(DateTime Time)
        {
            try
            { 
            var sql = $@"------=================日报，取本日的数据========================------
with cte as(
  select a.OperationDate,
         b.RegioneName,
         sum(a.DAchievAmt) as DAchievAmt,     --销售金额
         sum(a.LDAchievAmt) as LDAchievAmt,   --上期销售金额
         sum(a.DSaleQty) as DSaleQty,         --销售数量       
         sum(a.LDSaleQty) as LDSaleQty        --上期销售数量   
    from aps.aps_kind_sale_ratio as a
    left join dws.dws_dim_shop b on a.ShopID = b.ShopId
   where a.OperationDate = '{Time:yyyy-MM-dd}'     --传参，默认取昨天的日期
   group by a.OperationDate,
            b.RegioneName
          )
    ,cte1 as(
  select a.OperationDate,
         b.RegioneName,
         sum(a.DCT) as DCT,                 --客流量
         sum(a.LDCT) as LDCT,               --上期客流量
         sum(a.DCN) as DCN,                 --客单数
         sum(a.LDCN) as LDCN                --上期客单数
    from aps.aps_shop_cnct_ratio a
    left join dws.dws_dim_shop b on a.ShopID = b.ShopId
   where a.OperationDate = '{Time:yyyy-MM-dd}'     --传参，默认取昨天的日期
     and isnull(b.RegioneName,'') <> ''
   group by a.OperationDate,b.RegioneName
            )
  select to_char(cte.OperationDate,'YYYY-MM-DD') as OperationDate,                 --销售日期
         cte.RegioneName,                   --区域
         cte.DAchievAmt as AchievAmt,                    --销售金额
         cte.LDAchievAmt as LAchievAmt,                   --上期销售金额
         cte.DSaleQty as SaleQty,                      --销售数量
         cte.LDSaleQty as LSaleQty,                     --上期销售数量 
         cte1.DCT as CT,                          --客流量
         cte1.LDCT as LCT,                         --上期客流量
         cte1.DCN as CN,                          --客单数
         cte1.LDCN as LCN,                         --上期客单数
         cte1.DCN / case when cte1.DCT = 0 then 1 else cte1.DCT end  as ConverRate,                   --客单数 /客流量=转换率
         cte1.LDCN / case when cte1.LDCT = 0 then 1 else cte1.LDCT end  as LConverRate,                --客单数 /客流量=上期转换率
         cte.DSaleQty / case when cte1.DCN = 0 then 1 else cte1.DCN end  as JointRate,                --销售数量/客单数=连带率 
         cte.LDSaleQty / case when cte1.LDCN = 0 then 1 else cte1.LDCN end  as LJointRate,             --销售数量/客单数=上期连带率
         cte.DAchievAmt / case when cte.DSaleQty = 0 then 1 else cte.DSaleQty end  as UnitPrice,      --销售金额/销售数量=件单价
         cte.LDAchievAmt / case when cte.LDSaleQty = 0 then 1 else cte.LDSaleQty end  as LUnitPrice,    --销售金额/销售数量=上期件单价
         cte.DAchievAmt / case when cte1.DCN = 0 then 1 else cte1.DCN end as CustomerPrice,  --销售金额/客单数=客单价
         cte.LDAchievAmt / case when cte1.LDCN = 0 then 1 else cte1.LDCN end as LCustomerPrice  --环比上日客单价
    from cte
    left join cte1 on cte.OperationDate = cte1.OperationDate 
    and cte.RegioneName = cte1.RegioneName";
                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd, connection);
                var list = ModelConvertHelper<WxDataIndicatorsModel.AreaDataModel>.ConvertToModel(dt);
                return list;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 周报，取截止到本日的周累计数据
        /// </summary>
        /// <returns></returns>
        public IList<WxDataIndicatorsModel.AreaDataModel> GetWeekAreaData(DateTime Time)
        {
            try
            { 
            var sql = $@"------===============周报，取截止到本日的周累计数据================------
with cte as(
  select a.OperationDate,
         b.RegioneName,
         sum(a.NWAchievAmt) as NWAchievAmt,     --销售金额
         sum(a.LNWAchievAmt) as LNWAchievAmt,   --上期销售金额
         sum(a.NWSaleQty) as NWSaleQty,         --销售数量       
         sum(a.LNWSaleQty) as LNWSaleQty        --上期销售数量   
    from aps.aps_kind_sale_ratio as a
    left join dws.dws_dim_shop b on a.ShopID = b.ShopId
   where a.OperationDate = '{Time:yyyy-MM-dd}'         --传参，默认取昨天的日期
   group by a.OperationDate,
            b.RegioneName
          )
    ,cte1 as(
  select a.OperationDate,
         b.RegioneName,
         sum(a.NWCT) as NWCT,                 --客流量
         sum(a.LNWCT) as LNWCT,               --上期客流量
         sum(a.NWCN) as NWCN,                 --客单数
         sum(a.LNWCN) as LNWCN                --上期客单数
    from aps.aps_shop_cnct_ratio a
    left join dws.dws_dim_shop b on a.ShopID = b.ShopId
   where a.OperationDate = '{Time:yyyy-MM-dd}'       --传参，默认取昨天的日期
     and isnull(b.RegioneName,'') <> ''
   group by a.OperationDate,b.RegioneName
            )
  select to_char(cte.OperationDate,'YYYY-MM-DD') as OperationDate,                  --销售日期
         cte.RegioneName,                    --区域
         cte.NWAchievAmt as AchievAmt,                    --销售金额
         cte.LNWAchievAmt as LAchievAmt,                   --上期销售金额
         cte.NWSaleQty as SaleQty,                      --销售数量
         cte.LNWSaleQty as LSaleQty,                     --上期销售数量 
         cte1.NWCT as CT,                          --客流量
         cte1.LNWCT as LCT,                         --上期客流量
         cte1.NWCN as CN,                          --客单数
         cte1.LNWCN as LCN,                         --上期客单数
         cte1.NWCN / case when cte1.NWCT = 0 then 1 else cte1.NWCT end  as ConverRate,                   --客单数 /客流量=转换率
         cte1.LNWCN / case when cte1.LNWCT = 0 then 1 else cte1.LNWCT end  as LConverRate,                 --客单数 /客流量=上期转换率
         cte.NWSaleQty / case when cte1.NWCN = 0 then 1 else cte1.NWCN end  as JointRate,                --销售数量/客单数=连带率 
         cte.LNWSaleQty / case when cte1.LNWCN = 0 then 1 else cte1.LNWCN end  as LJointRate,             --销售数量/客单数=上期连带率
         cte.NWAchievAmt / case when cte.NWSaleQty = 0 then 1 else cte.NWSaleQty end  as UnitPrice,      --销售金额/销售数量=件单价
         cte.LNWAchievAmt / case when cte.LNWSaleQty = 0 then 1 else cte.LNWSaleQty end  as LUnitPrice ,   --销售金额/销售数量=上期件单价
         cte.NWAchievAmt / case when cte1.NWCN = 0 then 1 else cte1.NWCN end as CustomerPrice,  --销售金额/客单数=客单价
         cte.LNWAchievAmt / case when cte1.LNWCN = 0 then 1 else cte1.LNWCN end as LCustomerPrice  --环比上日客单价
    from cte
    left join cte1 on cte.OperationDate = cte1.OperationDate 
    and cte.RegioneName = cte1.RegioneName";
                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd, connection);
                var list = ModelConvertHelper<WxDataIndicatorsModel.AreaDataModel>.ConvertToModel(dt);
                return list;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 月报，取截止到本日的月累计数据
        /// </summary>
        /// <returns></returns>
        public IList<WxDataIndicatorsModel.AreaDataModel> GetMonthAreaData(DateTime Time)
        {
            try
            { 
            var sql = $@"------===============月报，取截止到本日的月累计数据================------
with cte as(
  select a.OperationDate,
         b.RegioneName,
         sum(a.MAchievAmt) as MAchievAmt,     --销售金额
         sum(a.LMAchievAmt) as LMAchievAmt,   --上期销售金额
         sum(a.MSaleQty) as MSaleQty,         --销售数量       
         sum(a.LMSaleQty) as LMSaleQty        --上期销售数量   
    from aps.aps_kind_sale_ratio as a
    left join dws.dws_dim_shop b on a.ShopID = b.ShopId
   where a.OperationDate = '{Time:yyyy-MM-dd}'     --传参，默认取昨天的日期
   group by a.OperationDate,
            b.RegioneName
          )
    ,cte1 as(
  select a.OperationDate,
         b.RegioneName,
         sum(a.MCT) as MCT,                 --客流量
         sum(a.LMCT) as LMCT,               --上期客流量
         sum(a.MCN) as MCN,                 --客单数
         sum(a.LMCN) as LMCN                --上期客单数
    from aps.aps_shop_cnct_ratio a
    left join dws.dws_dim_shop b on a.ShopID = b.ShopId
   where a.OperationDate = '{Time:yyyy-MM-dd}'     --传参，默认取昨天的日期
     and isnull(b.RegioneName,'') <> ''
   group by a.OperationDate,b.RegioneName
            )
  select to_char(cte.OperationDate,'YYYY-MM-DD') as OperationDate,               --销售日期
         cte.RegioneName,                 --区域
         cte.MAchievAmt as AchievAmt,                  --销售金额
         cte.LMAchievAmt as LAchievAmt,                 --上期销售金额
         cte.MSaleQty as SaleQty,                    --销售数量
         cte.LMSaleQty as LSaleQty,                   --上期销售数量 
         cte1.MCT as CT,                        --客流量
         cte1.LMCT as LCT,                       --上期客流量
         cte1.MCN as CN,                        --客单数
         cte1.LMCN as LCN,                        --上期客单数
         cte1.MCN / case when cte1.MCT = 0 then 1 else cte1.MCT end  as ConverRate,                   --客单数 /客流量=转换率
         cte1.LMCN / case when cte1.LMCT = 0 then 1 else cte1.LMCT end  as LConverRate,                --客单数 /客流量=上期转换率
         cte.MSaleQty / case when cte1.MCN = 0 then 1 else cte1.MCN end  as JointRate,                --销售数量/客单数=连带率 
         cte.LMSaleQty / case when cte1.LMCN = 0 then 1 else cte1.LMCN end  as LJointRate,             --销售数量/客单数=上期连带率
         cte.MAchievAmt / case when cte.MSaleQty = 0 then 1 else cte.MSaleQty end  as UnitPrice,      --销售金额/销售数量=件单价
         cte.LMAchievAmt / case when cte.LMSaleQty = 0 then 1 else cte.LMSaleQty end  as LUnitPrice,    --销售金额/销售数量=上期件单价
         cte.MAchievAmt / case when cte1.MCN = 0 then 1 else cte1.MCN end as CustomerPrice,  --销售金额/客单数=客单价
         cte.LMAchievAmt / case when cte1.LMCN = 0 then 1 else cte1.LMCN end as LCustomerPrice  --环比上日客单价
    from cte
    left join cte1 on cte.OperationDate = cte1.OperationDate 
    and cte.RegioneName = cte1.RegioneName";
                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd, connection);
                var list = ModelConvertHelper<WxDataIndicatorsModel.AreaDataModel>.ConvertToModel(dt);
                return list;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 月报，取截止到本日的月累计数据
        /// </summary>
        /// <returns></returns>
        public IList<WxDataIndicatorsModel.AreaDataModel> GetYearAreaData(DateTime Time)
        {
            try
            { 
            var sql = $@"------===============年报，取截止到本日的年累计数据================------
with cte as(
  select to_char(OperationDate,'YYYY') as OperationDate,
         b.RegioneName,
         sum(a.DAchievAmt) as DAchievAmt,     --销售金额
         0.0 as LDAchievAmt,                    --上期销售金额
         sum(a.DSaleQty) as DSaleQty,         --销售数量       
         0 as LDSaleQty                       --上期销售数量   
    from aps.aps_kind_sale_ratio as a
    left join dws.dws_dim_shop b on a.ShopID = b.ShopId
   where OperationDate >= '{Time.Year}-01-01'
     and OperationDate <= '{Time:yyyy-MM-dd}'        --传参，默认取昨天的日期
   group by to_char(a.OperationDate,'YYYY'),RegioneName
          )
    ,cte1 as(
  select to_char(OperationDate,'YYYY') as OperationDate,
         b.RegioneName,
         sum(a.DCT) as DCT,                 --客流量
         0 as LDCT,                         --上期客流量
         sum(a.DCN) as DCN,                 --客单数
         0 as LDCN                          --上期客单数
    from aps.aps_shop_cnct_ratio a
    left join dws.dws_dim_shop b on a.ShopID = b.ShopId
   where OperationDate >= '{Time.Year}-01-01'
     and OperationDate <= '{Time:yyyy-MM-dd}'     --传参，默认取昨天的日期
     and isnull(b.RegioneName,'') <> ''
   group by to_char(a.OperationDate,'YYYY'),b.RegioneName
            )
  select cte.OperationDate,               --销售日期
         cte.RegioneName,                 --区域
         cte.DAchievAmt as AchievAmt,                  --销售金额
         cte.LDAchievAmt as LAchievAmt,                 --上期销售金额
         cte.DSaleQty as SaleQty,                    --销售数量
         cte.LDSaleQty as LSaleQty,                   --上期销售数量 
         cte1.DCT as CT,                        --客流量
         cte1.LDCT as LCT,                       --上期客流量
         cte1.DCN as CN,                        --客单数
         cte1.LDCN as LCN,                        --上期客单数
         cte1.DCN / case when cte1.DCT = 0 then 1 else cte1.DCT end  as ConverRate,                   --客单数 /客流量=转换率
         0.0  as LConverRate,                                                                            --客单数 /客流量=上期转换率
         cte.DSaleQty / case when cte1.DCN = 0 then 1 else cte1.DCN end  as JointRate,                --销售数量/客单数=连带率 
         0.0  as LJointRate,                                                                             --销售数量/客单数=上期连带率
         cte.DAchievAmt / case when cte.DSaleQty = 0 then 1 else cte.DSaleQty end  as UnitPrice,      --销售金额/销售数量=件单价
         0.0  as LUnitPrice ,                                                                             --销售金额/销售数量=上期件单价
         cte.DAchievAmt / case when cte1.DCN = 0 then 1 else cte1.DCN end as CustomerPrice,  --销售金额/客单数=客单价
         cte.LDAchievAmt / case when cte1.LDCN = 0 then 1 else cte1.LDCN end as LCustomerPrice  --环比上日客单价
    from cte
    left join cte1 on cte.OperationDate = cte1.OperationDate 
    and cte.RegioneName = cte1.RegioneName";
                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd, connection);
                var list = ModelConvertHelper<WxDataIndicatorsModel.AreaDataModel>.ConvertToModel(dt);
                return list;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 日报，取本日的数据
        /// </summary>
        /// <param name="StartDate"></param>
        /// <param name="EndDate"></param>
        /// <returns></returns>
        public IList<WxDataIndicatorsModel.SeqTrendModel> GetDaySeqTrend(DateTime StartDate, DateTime EndDate)
        {
            try
            {
                var sql = $@"------=================日报，取本日的数据========================------
with cte as(
  select OperationDate,
  		 RegioneName,
         sum(DAchievAmt) as DAchievAmt,   --销售金额
         sum(DSaleQty) as DSaleQty        --销售数量       
    from aps.aps_kind_sale_ratio a
    left join dws.dws_dim_shop b on a.ShopID = b.ShopId
   where OperationDate >= '{StartDate:yyyy-MM-dd}'    --根据后面的日期往前推5天
     and OperationDate <= '{EndDate:yyyy-MM-dd}'     --传参，默认取昨天的日期
   group by OperationDate,RegioneName
          )
    ,cte1 as(
  select OperationDate,
  		 RegioneName,
         sum(DCT) as DCT,                 --客流量
         sum(DCN) as DCN                  --客单数
    from aps.aps_shop_cnct_ratio a
    left join dws.dws_dim_shop b on a.ShopID = b.ShopId
   where OperationDate >= '{StartDate:yyyy-MM-dd}'    --根据后面的日期往前推5天
     and OperationDate <= '{EndDate:yyyy-MM-dd}'       --传参，默认取昨天的日期
   group by OperationDate,RegioneName
            )
  select to_char(cte.OperationDate,'YYYY-MM-DD') as OperationDate,               --销售日期
         cte.RegioneName,                  --地区
         cte.DAchievAmt as AchievAmt,                  --销售金额
         cte.DSaleQty as SaleQty,                    --销售数量
         cte1.DCT as CT,                        --客流量
         cte1.DCN as CN,                        --客单数
         cte1.DCN / case when cte1.DCT = 0 then 1 else cte1.DCT end  as ConverRate,              --客单数 /客流量=转换率
         cte.DSaleQty / case when cte1.DCN = 0 then 1 else cte1.DCN end  as JointRate,           --销售数量/客单数=连带率 
         cte.DAchievAmt / case when cte.DSaleQty = 0 then 1 else cte.DSaleQty end  as UnitPrice,  --销售金额/销售数量=件单价
         cte.DAchievAmt / case when cte1.DCN = 0 then 1 else cte1.DCN end as CustomerPrice  --销售金额/客单数=客单价
    from cte
    left join cte1 on cte.OperationDate = cte1.OperationDate
    and cte.RegioneName = cte1.RegioneName";
                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd, connection);
                var list = ModelConvertHelper<WxDataIndicatorsModel.SeqTrendModel>.ConvertToModel(dt);
                return list.OrderBy(d => d.OperationDate).ToList();
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 周报，取本周的数据
        /// </summary>
        /// <param name="StartDate"></param>
        /// <param name="EndDate"></param>
        /// <returns></returns>
        public IList<WxDataIndicatorsModel.SeqTrendModel> GetWeekSeqTrend(IList<string> WeekList)
        {
            try
            {
                var sql = $@"------=================周报，取本周的数据========================------
with cte as(
  select YEAR(OperationDate)||'-'||RIGHT('00'||CAST(week(OperationDate) AS VARCHAR(50)),2) as OperationDate,
  		 RegioneName,
         sum(NWAchievAmt) as NWAchievAmt,   --销售金额
         sum(NWSaleQty) as NWSaleQty        --销售数量       
    from aps.aps_kind_sale_ratio a
    left join dws.dws_dim_shop b on a.ShopID = b.ShopId
   where OperationDate in ({(string.Join(",", WeekList))})    --根据后面的日期往前推5周
   group by year(OperationDate),week(OperationDate),RegioneName
          )
    ,cte1 as(
  select YEAR(OperationDate)||'-'||RIGHT('00'||CAST(week(OperationDate) AS VARCHAR(50)),2) as OperationDate,
  		 RegioneName,
         sum(NWCT) as NWCT,                 --客流量
         sum(NWCN) as NWCN                  --客单数
    from aps.aps_shop_cnct_ratio a
    left join dws.dws_dim_shop b on a.ShopID = b.ShopId
   where OperationDate in ({(string.Join(",", WeekList))})  --根据后面的日期往前推5天
   group by YEAR(OperationDate),week(OperationDate),RegioneName
            )
  select to_char(cte.OperationDate) as OperationDate,               --销售日期
         cte.RegioneName,                  --区域
         cte.NWAchievAmt as AchievAmt,                  --销售金额
         cte.NWSaleQty as SaleQty,                    --销售数量
         cte1.NWCT as CT,                        --客流量
         cte1.NWCN as CN,                        --客单数
         cte1.NWCN / case when cte1.NWCT = 0 then 1 else cte1.NWCT end  as ConverRate,              --客单数 /客流量=转换率
         cte.NWSaleQty / case when cte1.NWCN = 0 then 1 else cte1.NWCN end  as JointRate,           --销售数量/客单数=连带率 
         cte.NWAchievAmt / case when cte.NWSaleQty = 0 then 1 else cte.NWSaleQty end  as UnitPrice,  --销售金额/销售数量=件单价
         cte.NWAchievAmt / case when cte1.NWCN = 0 then 1 else cte1.NWCN end as CustomerPrice  --销售金额/客单数=客单价
    from cte
    left join cte1 on cte.OperationDate = cte1.OperationDate
    and cte.RegioneName = cte1.RegioneName";
                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd, connection);
                var list = ModelConvertHelper<WxDataIndicatorsModel.SeqTrendModel>.ConvertToModel(dt);
                return list.OrderBy(d=>d.OperationDate).ToList();
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 月报，取本月的数据
        /// </summary>
        /// <param name="StartDate"></param>
        /// <param name="EndDate"></param>
        /// <returns></returns>
        public IList<WxDataIndicatorsModel.SeqTrendModel> GetMonthSeqTrend(IList<string> MonthList)
        {
            try
            {
                var sql = $@"------=================月报，取本月的数据========================------
with cte as(
  select YEAR(OperationDate)||'-'||RIGHT('00'||CAST(MONTH(OperationDate) AS VARCHAR(50)),2) as OperationDate,
  		 RegioneName,
         sum(MAchievAmt) as MAchievAmt,   --销售金额
         sum(MSaleQty) as MSaleQty        --销售数量       
    from aps.aps_kind_sale_ratio a
    left join dws.dws_dim_shop b on a.ShopID = b.ShopId
   where OperationDate in ({(string.Join(",", MonthList))})  --根据后面的日期往前推5周
   group by year(OperationDate),MONTH(OperationDate),RegioneName
          )
    ,cte1 as(
  select YEAR(OperationDate)||'-'||RIGHT('00'||CAST(MONTH(OperationDate) AS VARCHAR(50)),2) as OperationDate,
  		 RegioneName,
         sum(MCT) as MCT,                 --客流量
         sum(MCN) as MCN                  --客单数
    from aps.aps_shop_cnct_ratio a
    left join dws.dws_dim_shop b on a.ShopID = b.ShopId
   where OperationDate in ({(string.Join(",", MonthList))})  --根据后面的日期往前推5天
   group by YEAR(OperationDate),MONTH(OperationDate),RegioneName
            )
  select to_char(cte.OperationDate) as OperationDate,               --销售日期
         cte.RegioneName,                  --区域
         cte.MAchievAmt as AchievAmt,                  --销售金额
         cte.MSaleQty as SaleQty,                    --销售数量
         cte1.MCT as CT,                        --客流量
         cte1.MCN as CN,                        --客单数
         cte1.MCN / case when cte1.MCT = 0 then 1 else cte1.MCT end  as ConverRate,              --客单数 /客流量=转换率
         cte.MSaleQty / case when cte1.MCN = 0 then 1 else cte1.MCN end  as JointRate,           --销售数量/客单数=连带率 
         cte.MAchievAmt / case when cte.MSaleQty = 0 then 1 else cte.MSaleQty end  as UnitPrice,  --销售金额/销售数量=件单价
         cte.MAchievAmt / case when cte1.MCN = 0 then 1 else cte1.MCN end as CustomerPrice  --销售金额/客单数=客单价
    from cte
    left join cte1 on cte.OperationDate = cte1.OperationDate
    and cte.RegioneName = cte1.RegioneName";
                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd, connection);
                var list = ModelConvertHelper<WxDataIndicatorsModel.SeqTrendModel>.ConvertToModel(dt);
                return list.OrderBy(d => d.OperationDate).ToList();
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }
    }
}
